//Copyrights (c) Microsoft Corporation. All rights reserved.


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using System.IO;
using Microsoft.SqlServer.Management.Trace;
using Microsoft.SqlServer.Management.Common;
using Microsoft.ApplicationBlocks.Data;
using System.Xml;
using System.Text.RegularExpressions;
using Microsoft.AnalysisServices.Xmla;
using ObjectSerializer;

namespace MDXParameter
{
    public partial class Main : Form
    {
        private DlgSetup DialogLogin; //a reference to the child form to prompt for connection info

        Thread worker;
        TraceTable tableWriter;
        DataSet ds;
        string OriginalMDX = "";
        
        DataView dv;
        string traceServer = "localhost"; 
        string sqlServer = "localhost";
        string dbName = "MDX";
        string tableName = "MDXTraceQueryStart"; 
        string traceStatus="stop";
        public Main()
        {
            InitializeComponent();
        }

        private void btnStartTrace_Click(object sender, EventArgs e)
        {            
                StartStopTrace();          
        }

        private void StartStopTrace()
        {
            if (traceStatus == "stop")
            {
                //try to start trace.

                OlapConnectionInfo ci = new OlapConnectionInfo();
                TraceServer olpaTraceServer = new TraceServer();

                ci.UseIntegratedSecurity = true;
                ci.ServerName= traceServer;
                string tracetemplate = "QueryBegin.tdf";
                if (!File.Exists(tracetemplate))
                {
                    MessageBox.Show("Profiler trace template QueryBegin.tdf is not found in current directory");
                    return;
                }
                olpaTraceServer.InitializeAsReader(ci, tracetemplate);
                SqlConnectionInfo connInfo = new SqlConnectionInfo(sqlServer);
                tableWriter = new TraceTable();

                connInfo.DatabaseName = dbName;
                tableWriter.InitializeAsWriter(olpaTraceServer, connInfo, tableName);
                worker = new Thread(DoWork);
                worker.Start();
                traceStatus = "start";
                Status.Items["lblProfilerStatus"].Text = "Profiler Trace Running on SSAS server " + traceServer;
                traceToolStripMenuItem.DropDownItems["startToolStripMenuItem"].Enabled = false;
                traceToolStripMenuItem.DropDownItems["stopToolStripMenuItem"].Enabled = true;

                picRunning.Visible = true;
                btnStartTrace.Text = "Stop Trace";
                btnModifyQuery.Visible = false;
                pnlModifySQL.Visible = false;
                btnModifyQuery.Visible = false;
                grdProfilerResults.Enabled = false;
                btnExecMDX.Enabled = false;
            }
            else
            {
                traceStatus = "stopping";
                Status.Items["lblProfilerStatus"].Text = "Profiler Trace Stopping";

                //stop the trace

                worker.Abort();
               
                traceStatus = "stop";
                Status.Items["lblProfilerStatus"].Text = "Profiler Trace Stopped";
                traceToolStripMenuItem.DropDownItems["startToolStripMenuItem"].Enabled = true;
                traceToolStripMenuItem.DropDownItems["stopToolStripMenuItem"].Enabled = false;

                picRunning.Visible = false;
                btnStartTrace.Text = "Start Trace";
                btnModifyQuery.Visible = true;
                txbProfilerSQLQuery.Text = "select [RowNumber],[TextData],[RequestParameters],[EventClass],[ApplicationName],[ClientProcessID],[ConnectionID],[CurrentTime],[DatabaseName],[EventSubclass],[NTCanonicalUserName],[NTDomainName],[NTUserName],[RequestProperties],[SPID],[ServerName],[SessionID],[StartTime],[CPUTime],[Duration],[EndTime],[Error],[Severity],[Success] from " + tableName + " where RowNumber<>0";

                DisplayProfilerTraceResult();

                btnModifyQuery.Visible = true;
                grdProfilerResults.Enabled = true;
                btnExecMDX.Enabled = true;
            }
        }

        void DoWork()
        {
            while (traceStatus != "stopping" && traceStatus != "stop")
            {
                try
                { tableWriter.Write(); }
                catch{continue;}
            }
        }

        private void DisplayProfilerTraceResult()
        {
                //display trace result

                string ProfilerTableConnectionString = "Data Source=" + sqlServer + ";initial catalog=" + dbName + "; Integrated Security=True;";
                ds = SqlHelper.ExecuteDataset(ProfilerTableConnectionString, CommandType.Text, txbProfilerSQLQuery.Text);
                dv=new DataView( ds.Tables[0],"EventClass=9","",DataViewRowState.CurrentRows);
                grdProfilerResults.DataSource = dv;
                grdProfilerResults.AutoResizeColumns(
                    DataGridViewAutoSizeColumnsMode.ColumnHeader);
                grdProfilerResults.AllowUserToResizeColumns = true;
                grdProfilerResults.Columns[0].Width = 60;
                grdProfilerResults.Columns[1].Width = 450;
                grdProfilerResults.Columns[2].Width = 250;
               
        }

        private void btnModifyQuery_Click(object sender, EventArgs e)
        {
            pnlModifySQL.Visible = true;
        }

        private void btnRefreshProfilerSQLResult_Click(object sender, EventArgs e)
        {
            DisplayProfilerTraceResult();
            pnlModifySQL.Visible = false;
        }

        private void grdProfilerResults_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            if ( dv.Count==0)
            {
                //do nothing if there is no row return
                return;
            }
            label1.Text = e.RowIndex.ToString() + " entered";
            int MDXColumnIndex = -1;
            int InputParameterColumnIndex = -1;
            MDXColumnIndex = ds.Tables[0].Columns.IndexOf("TextData");
            InputParameterColumnIndex = ds.Tables[0].Columns.IndexOf("RequestParameters");
            if (MDXColumnIndex == -1)
            {
                //show error
                MessageBox.Show("Cannot find column TextData, please check your SQL query");
            }
            else
            {
                //display MDX query in the text box
                string Parameters=dv[e.RowIndex].Row.ItemArray.GetValue(InputParameterColumnIndex).ToString();
                txbParameters.Text = Parameters.Replace("\n", "\n\r\n");
                OriginalMDX = dv[e.RowIndex].Row.ItemArray.GetValue(MDXColumnIndex).ToString();
                if (Parameters.Trim() != "")
                {
                    txbMDX.Text = DisplayMDXParameter(dv[e.RowIndex].Row.ItemArray.GetValue(MDXColumnIndex).ToString(), Parameters).Replace("\n","\n\r\n");
                 }
                else
                {
                    //there is no parameter value, there no need to substitude the parameters
                    txbMDX.Text = dv[e.RowIndex].Row.ItemArray.GetValue(MDXColumnIndex).ToString().Replace("\n", "\n\r\n");
                    dgvParameters.DataSource=new DataTable();
                }
                //save the 
                lblCurrentOLAPDatabase.Text = dv[e.RowIndex].Row.ItemArray.GetValue(dv.Table.Columns.IndexOf("DatabaseName")).ToString();
            }
        }



        private string DisplayMDXParameter(string MDX,string Parameters)
        {            

            if (cbxReplaceParameter.Checked && txbParameters.Text.Length>0)
            {             
                //The parameters are in format of XML, and it is as simple as name and value pair. The easiest way to handle it is to convert it into a data set

                DataSet dsParameter = new DataSet(); 
                //the dataset is alway a single table with 2 columns, first column is the parameter name, the second is the parameter value
                dsParameter.ReadXml(new XmlTextReader(new StringReader(Parameters)));
               
                //we need to create a dataview that is sorted by name desc. This is because we do not want to overwrite @CityCode with @City accidentally

                DataView dvParameter = new DataView(dsParameter.Tables[0], "", "Name DESC", DataViewRowState.CurrentRows);

                for (int i = 0; i < dvParameter.Count; i++)
                {
                    //string replace should be case insensitiv
                    //reference http://weblogs.asp.net/jgalloway/archive/2004/02/11/71188.aspx

                    MDX = Regex.Replace(MDX, "@" + dvParameter[i][0].ToString(), "\"" + dvParameter[i][1].ToString() + "\"", RegexOptions.IgnoreCase);
                }
                dgvParameters.DataSource = dsParameter.Tables[0];
                dgvParameters.AutoResizeColumns(
               DataGridViewAutoSizeColumnsMode.ColumnHeader);
                dgvParameters.AllowUserToResizeColumns = true;
                dgvParameters.Columns[1].Width = 600;

                return MDX;
            }
            else
            {
                return MDX;
            }
        }

        private void cbxReplaceParameter_CheckedChanged(object sender, EventArgs e)
        {
            if (!cbxReplaceParameter.Checked)
            { txbMDX.Text = OriginalMDX; }
              
        }

        private void btnCopy_Click(object sender, EventArgs e)
        {
           
        }

        private void Main_Load(object sender, EventArgs e)
        {
             this.Show();
             lblCurrentOLAPDatabase.Visible = false;
             ShowSetupDialog();

        }
        private void ShowSetupDialog()
        {
            DialogResult Result;
           
            this.DialogLogin = new DlgSetup(traceServer, sqlServer, dbName, tableName);
            Result = DialogLogin.ShowDialog();


            if (Result == DialogResult.OK)
            {
                this.traceServer = DialogLogin.txbTraceServer.Text;
                this.sqlServer = DialogLogin.txbSQLServer.Text;
                this.dbName = DialogLogin.txbDBName.Text;
                this.tableName = DialogLogin.txbTableName.Text;
            }
            else
            {
            }
        }

        private void connectionToolStripMenuItem_Click(object sender, EventArgs e)
        {
            ShowSetupDialog();
        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.Close();

        }

        private void startToolStripMenuItem_Click(object sender, EventArgs e)
        {
            StartStopTrace();
        }

        private void stopToolStripMenuItem_Click(object sender, EventArgs e)
        {
            StartStopTrace();
        }

        private void txbMDX_KeyDown(object sender, KeyEventArgs e)
        {
            //process the key button press for Ctrl-A and Ctrl-C

            if (e.Control && e.KeyCode == Keys.A )
            {
                txbMDX.SelectAll();
            }
            else if (e.Control & e.KeyCode == Keys.C )
            {
                Clipboard.SetDataObject(txbMDX.SelectedText, true); 
            }
        }

        private void txbParameters_KeyDown(object sender, KeyEventArgs e)
        {
            //process the key button press for Ctrl-A and Ctrl-C

            if (e.Control && e.KeyCode == Keys.A)
            {
                txbParameters.SelectAll();
            }
            else if (e.Control & e.KeyCode == Keys.C)
            {
                Clipboard.SetDataObject(txbParameters.SelectedText, true);
            }
        }

        private void txbMDX_TextChanged(object sender, EventArgs e)
        {
            //only display the Execute MDX button when there is text in this box
            if (txbMDX.Text != "")
            {
                btnExecMDX.Visible = true;
            }
            else
            {
                btnExecMDX.Visible = false;
            }
        }

        private void btnExecuteMDX_Click(object sender, EventArgs e)
        {
           
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string response = null;
            string connectionString = "Impersonation Level=Impersonate;Data Source=" + traceServer + ";Initial Catalog=" + lblCurrentOLAPDatabase.Text;
            XmlaClient xmlaClient = new XmlaClient();
            try
            {
                xmlaClient.Connect(connectionString, true);
                response = xmlaClient.Send(txbMDX.Text, null);
            }
            finally
            {
                xmlaClient.Disconnect();
            }

            //this.tabMDXResult.Controls.Add(ASControlsWrapper.ASControls.GetQueryGrid(response));
        }

        private void btnExecMDX_Click(object sender, EventArgs e)
        {
            MessageBox.Show("this function is not working yet");
            this.tabMDXResult.Controls.Clear();

            
                string response = null;
                string connectionString = "Data Source=" + traceServer+ ";Initial Catalog="+lblCurrentOLAPDatabase.Text;
                XmlaClient xmlaClient = new XmlaClient();
                try
                {
                    xmlaClient.Connect(connectionString, true);
                    //response = xmlaClient.Send(sampleRequest, null);
                    xmlaClient.ExecuteStatement(txbMDX.Text, out response, false, "", "");
                }
                finally
                {
                    xmlaClient.Disconnect();
                }
                Clipboard.SetDataObject(response, true);
                //this.tabMDXResult.Controls.Add(ASControlsWrapper.ASControls.GetQueryGrid(response));
            
        }

        private void wordWrapToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (wordWrapToolStripMenuItem.Checked)
            {
                wordWrapToolStripMenuItem.Checked = false;
                txbMDX.WordWrap = false;
                txbParameters.WordWrap = false;

            }
            else
            {
                wordWrapToolStripMenuItem.Checked = true;
                txbMDX.WordWrap = true;
                txbParameters.WordWrap = true;
            }
        }

        private void btnQueryCancel_Click(object sender, EventArgs e)
        {
            pnlModifySQL.Visible = false;
        }

       

        private void saveFileToolStripMenuItem_Click(object sender, EventArgs e)
        {
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
         
            saveFileDialog1.Title = "Save current trace infromation into a file";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string SaveFilePath = saveFileDialog1.FileName;
                Serializer.SerializeObject(SaveFilePath,(object) ds );
                Status.Items["lblProfilerStatus"].Text = "File saved on " + SaveFilePath;
            }
       
        }

        private void openFileToolStripMenuItem_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
           
            openFileDialog1.Title = "Select a file to load the trace information. The file should not be a Profiler trace file. It should be a serialized DataSet object";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string OpenFilePath = openFileDialog1.FileName;
                ds=(DataSet) Serializer.DeSerializeObject(OpenFilePath);

                if (viewQueryEndToolStripMenuItem.Checked)
                {
                    dv = new DataView(ds.Tables[0], "", "", DataViewRowState.CurrentRows);
                }
                else
                {
                    dv = new DataView(ds.Tables[0], "EventClass=9", "", DataViewRowState.CurrentRows);
                }
                
                grdProfilerResults.DataSource = dv;
                grdProfilerResults.AutoResizeColumns(
                    DataGridViewAutoSizeColumnsMode.ColumnHeader);
                grdProfilerResults.AllowUserToResizeColumns = true;
                grdProfilerResults.Columns[0].Width = 60;
                grdProfilerResults.Columns[1].Width = 450;
                grdProfilerResults.Columns[2].Width = 250;
                Status.Items["lblProfilerStatus"].Text = "File open on " + OpenFilePath;
            }

        }

        private void viewQueryEndToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (ds==null)
            {  return;}
            if (viewQueryEndToolStripMenuItem.Checked)
            {
                viewQueryEndToolStripMenuItem.Checked = false;
                dv = new DataView(ds.Tables[0], "EventClass=9", "", DataViewRowState.CurrentRows);
            }
            else
            {
                viewQueryEndToolStripMenuItem.Checked = true;
                dv = new DataView(ds.Tables[0], "", "", DataViewRowState.CurrentRows);
            } 
            grdProfilerResults.DataSource = dv;
                grdProfilerResults.AutoResizeColumns(
                    DataGridViewAutoSizeColumnsMode.ColumnHeader);
                grdProfilerResults.AllowUserToResizeColumns = true;
                grdProfilerResults.Columns[0].Width = 60;
                grdProfilerResults.Columns[1].Width = 450;
                grdProfilerResults.Columns[2].Width = 250;
        }




        }
    }
